--select top 10 * from CHARTIS_CarCoverCode 
--select top 10 * from CHARTIS_CarCoverList
--select top 10 * from CHARTIS_CarCoverType 
--select top 10 * from CHARTIS_CarF1 
--select top 10 * from dbo.tblRateVehicleUsage Order By ModifiedDate DESC
--select top 10 * from dbo.tblRateVehicleAge Order By ModifiedDate DESC
--select top 10 * from dbo.tblRateEngine Order By ModifiedDate DESC
--select top 10 * from dbo.tblRateSumInsured Order By ModifiedDate DESC
--select top 10 * from dbo.tblProviderCompany Where CompanyName Like 'Chartis'
Declare @CarId bigint
Declare @DOB date
Declare @VehicleManYear int
Declare @VehicleCode int
Declare @VehicleType int
Declare @VehicleCC int
Declare @VehicleAge int
Declare @DriverAge int
Declare @SumInsured int
Declare @VehicleGroupNo int
Declare @GarageType int
Declare @IsClaim bit
Declare @IsAddDriver bit
Declare @NoOfDriver int
Declare @IsCTPL bit

Declare @NCB int
Declare @Fleet int
Declare @IsRedPlate bit
Declare @ProviderCompanyId int
Declare @ClassNo int

Declare @CoverTypeId int
--Declare @CoverListId int
Declare @TPBIPerPerson int
Declare @TPBIPerOccurance int
Declare @TPPD int
Declare @BasePremium float
Declare @BailBond float
Declare @PADriver float
Declare @PAPassenger float
Declare @MED float
Declare @DEC float
Declare @Seat int
Declare @F1 float
Declare @F2 float
Declare @F3 float
Declare @F4 float
Declare @F5 float
Declare @F6 float
Declare @F7 float
Declare @F8 float
Declare @F9 float
Declare @F10NCB float
Declare @F11Fleet float
Declare @F12Other float

Declare @GPW float
Declare @DutyStamp float
Declare @VAT float
Declare @TotalPremium float

Set @CarId = 3
Set @ProviderCompanyId = 31
--Set @VehicleCode = 110
--Set @ClassNo = 2
--Set @ProviderCompanyId = 31
--Set @VehicleCC = 3000
--Set @DriverAge = 34
--Set @VehicleAge = 5
--Set @SumInsured = 50000
--Set @VehicleGroupNo = 1
--Set @NCB = 20
--Set @Fleet = 10
--Set @IsRedPlate = 1
           
            
--Select CECarId, RefCEUserId, CarMakeId, CarModelId, ManufactYear , Info5 Email, Info6 MobileNo, Info7 SurName, Info8 FirstName, Info9 Gender, 
--            Info10 DOB, Info11 Occupation, Info12 JobNature, Info13 MaritalStatus,
--            Info14 DriveExp, ModifiedDate from tblCarDetail Where CECarId = 2055
                    
--Select Year(CONVERT(Date, '02-Feb-1984'))

Select @VehicleCC = cdth.CC, @VehicleGroupNo = cvg.VehicleGroup 
from tblCarDetailTH cdth INNER JOIN tblCarVehicleGroup cvg 
ON cdth.CarMake = cvg.MakeCode And cdth.CarModel = cvg.FamilyDesc 
And cdth.Body = cvg.BodyStyleDescription And cdth.CC = cvg.EngineSize
And cdth.Door = cvg.DoorNum
Where cdth.CarId = @CarId

Select top 10 * from tblCarDetailTH Where CarId = @CarId
--Select top 10 * from tblCarPolicy Where CECarId = 2055

Select @VehicleManYear = cd.ManufactYear, @DOB = CONVERT(Date, cd.Info10)
from tblCarDetailTH cd
Where CarId = @CarId

Select @VehicleAge = Year(GetDate()) - @VehicleManYear + 1

Select @DriverAge = (Year(GetDate()) - Year(CONVERT(Date, @DOB)))

Select @VehicleAge VehicleAge, @DriverAge Driverge, @VehicleCC CC, @VehicleGroupNo VehicleGroupNo

Select @VehicleCode = VehicleType, @ClassNo = InsuranceType, @GarageType = GarageType,
@SumInsured = SumInsured, @NCB = NCDPercent, @IsRedPlate = IsRedPlate, @IsClaim = IsClaimLastYear, 
@IsAddDriver = IsAddDriver, @NoOfDriver = NoOfDriver, @IsCTPL = IsCTPL from tblCarPolicy Where CECarId = @CarId

IF @ClassNo >= 10 
	BEGIN
		SET @ClassNo = 1
	END
ELSE IF @ClassNo >= 20 
	BEGIN
		SET @ClassNo = 2
	END
ELSE IF @ClassNo > 30 
	BEGIN
		SET @ClassNo = 3
	END

Select @VehicleCode VehicleCode, @ClassNo ClassNo, @GarageType GarageType,
@SumInsured SumInsured, @NCB NCDPercent, @IsRedPlate IsRedPlate, @IsClaim IsClaimLastYear, 
@IsAddDriver IsAddDriver, @NoOfDriver NoOfDriver, @IsCTPL IsCTPL

--Select * from CHARTIS_CarCoverType
--Select * from CHARTIS_CarCoverList
--Select * from CHARTIS_CarCoverCode
--Declare curCoverList CURSOR For Select CoverListId from CHARTIS_CarCoverList Where Status = 1

--Open curCoverList 
--FETCH NEXT from curCoverList INTO @CoverListId

--	WHILE (@@fetch_status <> -1)
--BEGIN	

	Select @CoverTypeId = CoverTypeId, @BasePremium = BasePremium, @BailBond = BailBond, @TPBIPerPerson = TPBIPP, @TPBIPerOccurance = TPBIPA, @TPPD = TPPD,
	@PADriver = PADriver, @PAPassenger = PAPassenger, @MED = MED, @DEC = DEC, @Seat = Seat, @Fleet = 0
	FROM CHARTIS_CarCoverType WHERE CoverListId = @ClassNo And CoverCode = @VehicleCode And InsuredAgeFrom <= @DriverAge 
	And InsuredAgeTo >= @DriverAge And SumInsuredFrom <= @SumInsured And SumInsuredTo >= @SumInsured
	And Status = 1

	Select * from CHARTIS_CarCoverList Where CoverListId = @ClassNo
	
	Select *
	FROM CHARTIS_CarCoverType WHERE CoverListId = @ClassNo And CoverCode = @VehicleCode And InsuredAgeFrom <= @DriverAge 
	And InsuredAgeTo >= @DriverAge And SumInsuredFrom <= @SumInsured And SumInsuredTo >= @SumInsured
	And Status = 1

	Select @CoverTypeId CoverTypeId, @BasePremium BasePremium, @BailBond BailBond, @TPBIPerPerson TPBIPP, @TPBIPerOccurance TPBIPA, @TPPD TPPD,
	@PADriver PADriver, @PAPassenger PAPassenger, @MED MED, @DEC DEC, @Seat Seat

	IF @ClassNo = 1
		BEGIN
			--F1
			Select @VehicleCode VehicleCode, @ProviderCompanyId ProviderCompanyId 
			select @VehicleType = Type, @F1 = Class1 from dbo.tblRateVehicleUsage Where VehicleCode = @VehicleCode And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F2
			IF @VehicleCC <= 2000 
			BEGIN
				select @F2 = Class1 from dbo.tblRateEngine Where Type = @VehicleType And CCCode = 2000 And ProviderCompanyId = @ProviderCompanyId And Status = 1
			END
			ELSE IF @VehicleCC > 2000
			BEGIN
				select @F2 = Class1 from dbo.tblRateEngine Where Type = @VehicleType And CCCode = 2001 And ProviderCompanyId = @ProviderCompanyId And Status = 1
			END
			--F3
			select @F3 = Class1 from tblRateDriverAge Where Type = @VehicleType And FromAge <= @DriverAge And ToAge >= @DriverAge And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F4
			select @F4 = Class1 from dbo.tblRateVehicleAge Where Type = @VehicleType And VehicleAge = @VehicleAge And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F5
			select Top 1 @F5 = Class1 from tblRateSumInsured Where Type = @VehicleType And  SumInsured >= @SumInsured And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F6
			select @F6 = Class1 from tblRateGroup Where Type = @VehicleType And GroupCode = @VehicleGroupNo And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F7
			select @F7 = Class1 from tblRateTPBIPerPerson Where Type = @VehicleType And TPBIPerPerson >= @TPBIPerPerson And TPBIPerPerson <= @TPBIPerOccurance And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F8
			select @F8 = Class1 from tblRateTPBIPerAccident Where Type = @VehicleType And TPBIPerAccident >= @TPBIPerOccurance And TPBIPerAccident <= @TPBIPerOccurance And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F9
			select @F9 = Class1 from tblRateTPPD Where Type = @VehicleType And TPPD >= @TPPD And TPPD <= @TPPD And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F10 NCB
			select @F10NCB = Class1 from tblRateNCB Where DiscountCode = @NCB And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F11 Fleet
			select @F11Fleet = Class1 from tblRateFleet Where DiscountCode = @Fleet And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F12 Other
			IF @IsRedPlate = 1
				BEGIN
					select @F12Other = Class1 from tblRateOtherDiscount Where DiscountCode = 18 And ProviderCompanyId = @ProviderCompanyId And Status = 1
				END
			ELSE
				BEGIN 
					SET @F12Other = 1
				END
				
		END
	ELSE IF @ClassNo = 2
		BEGIN
			Select @VehicleCode VehicleCode, @ProviderCompanyId ProviderCompanyId
			--F1
			select @VehicleType = Type, @F1 = Class2 from dbo.tblRateVehicleUsage Where VehicleCode = @VehicleCode And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F2
			IF @VehicleCC <= 2000 
			BEGIN
				select @F2 = Class2 from dbo.tblRateEngine Where Type = @VehicleType And CCCode = 2000 And ProviderCompanyId = @ProviderCompanyId And Status = 1
			END
			ELSE IF @VehicleCC > 2000
			BEGIN
				select @F2 = Class2 from dbo.tblRateEngine Where Type = @VehicleType And CCCode = 2001 And ProviderCompanyId = @ProviderCompanyId And Status = 1
			END
			--F3
			select @F3 = Class2 from tblRateDriverAge Where Type = @VehicleType And FromAge <= @DriverAge And ToAge >= @DriverAge And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F4
			select @F4 = Class2 from dbo.tblRateVehicleAge Where Type = @VehicleType And VehicleAge = @VehicleAge And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F5
			select Top 1 @F5 = Class2 from tblRateSumInsured Where Type = @VehicleType And SumInsured >= @SumInsured And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F6
			select @F6 = Class2 from tblRateGroup Where Type = @VehicleType And GroupCode = @VehicleGroupNo And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F7
			select @F7 = Class2 from tblRateTPBIPerPerson Where Type = @VehicleType And TPBIPerPerson >= @TPBIPerPerson And TPBIPerPerson <= @TPBIPerOccurance And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F8
			select @F8 = Class2 from tblRateTPBIPerAccident Where Type = @VehicleType And TPBIPerAccident >= @TPBIPerOccurance And TPBIPerAccident <= @TPBIPerOccurance And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F9
			select @F9 = Class2 from tblRateTPPD Where Type = @VehicleType And TPPD >= @TPPD And TPPD <= @TPPD And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F10 NCB
			select @F10NCB = Class2 from tblRateNCB Where DiscountCode = @NCB And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F11 Fleet
			select @F11Fleet = Class2 from tblRateFleet Where DiscountCode = @Fleet And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F12 Other
			IF @IsRedPlate = 1
				BEGIN
					select @F12Other = Class2 from tblRateOtherDiscount Where DiscountCode = 18 And ProviderCompanyId = @ProviderCompanyId And Status = 1
				END
			ELSE
				BEGIN 
					SET @F12Other = 1
				END
		END
	ELSE IF @ClassNo = 3
		BEGIN
			Select @VehicleCode VehicleCode, @ProviderCompanyId ProviderCompanyId
			--F1
			select @VehicleType = Type, @F1 = Class3 from dbo.tblRateVehicleUsage Where VehicleCode = @VehicleCode And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F2
			IF @VehicleCC <= 2000 
			BEGIN
				select @F2 = Class3 from dbo.tblRateEngine Where Type = @VehicleType And CCCode = 2000 And ProviderCompanyId = @ProviderCompanyId And Status = 1
			END
			ELSE IF @VehicleCC > 2000
			BEGIN
				select @F2 = Class3 from dbo.tblRateEngine Where Type = @VehicleType And CCCode = 2001 And ProviderCompanyId = @ProviderCompanyId And Status = 1
			END
			--F3
			select @F3 = Class3 from tblRateDriverAge Where Type = @VehicleType And FromAge <= @DriverAge And ToAge >= @DriverAge And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F4
			select @F4 = Class3 from dbo.tblRateVehicleAge Where Type = @VehicleType And VehicleAge = @VehicleAge And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F5
			select Top 1 @F5 = Class3 from tblRateSumInsured Where Type = @VehicleType And SumInsured >= @SumInsured And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F6
			select @F6 = Class3 from tblRateGroup Where Type = @VehicleType And GroupCode = @VehicleGroupNo And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F7
			select @F7 = Class3 from tblRateTPBIPerPerson Where Type = @VehicleType And TPBIPerPerson >= @TPBIPerPerson And TPBIPerPerson <= @TPBIPerOccurance And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F8
			select @F8 = Class3 from tblRateTPBIPerAccident Where Type = @VehicleType And TPBIPerAccident >= @TPBIPerOccurance And TPBIPerAccident <= @TPBIPerOccurance And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F9
			select @F9 = Class3 from tblRateTPPD Where Type = @VehicleType And TPPD >= @TPPD And TPPD <= @TPPD And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F10 NCB
			select @F10NCB = Class3 from tblRateNCB Where DiscountCode = @NCB And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F11 Fleet
			select @F11Fleet = Class3 from tblRateFleet Where DiscountCode = @Fleet And ProviderCompanyId = @ProviderCompanyId And Status = 1
			--F12 Other
			IF @IsRedPlate = 1
				BEGIN
					select @F12Other = Class3 from tblRateOtherDiscount Where DiscountCode = 18 And ProviderCompanyId = @ProviderCompanyId And Status = 1
				END
			ELSE
				BEGIN 
					SET @F12Other = 1
				END
		END
		
		Select @F1 F1, @F2 F2, @F3 F3, @F4 F4, @F5 F5, @F6 F6, @F7 F7, @F8 F8, @F9 F9, @F10NCB F10NCD, @F11Fleet F1Fleet, @F12Other F12Other 

		Select @GPW = @BasePremium * @F1 * @F2 * @F3 * @F4 * @F5 * @F6 * @F7 * @F8 * @F9
		
		Select @GPW = @GPW + (@PADriver + @PAPassenger + @MED + @BailBond)
		
		Select @GPW = @GPW * (@F10NCB * @F11Fleet * @F12Other)
		
		Select @DutyStamp = @GPW * (0.4 / 100)
		
		Declare @TempDutyStamp int
		Select @TempDutyStamp = CAST(@DutyStamp AS INT)
		
		IF (@TempDutyStamp < @DutyStamp)
		BEGIN
			SET @TempDutyStamp = @TempDutyStamp + 1
		END
		
		Select @DutyStamp OrgDutyStamp, @TempDutyStamp TempDutyStamp
		
		Select @DutyStamp = @TempDutyStamp
		
		Select @VAT = (@GPW + @DutyStamp) * 0.07
		
		Select @TotalPremium = @GPW + @DutyStamp + @VAT
		
		Select @GPW GPW, @DutyStamp DutyStamp, @VAT VAT, @TotalPremium TotalPremium
		
		--FETCH NEXT FROM curCoverList INTO @CoverListId
	--END
	
	
	--CLOSE curCoverList
	--Deallocate curCoverList
	
	-- select top 10 * from tblCarInsuranceTempResultTH